<@pp.dropOutputFile/>
<#include "../common/util.ftl">
<#if doc.tablePrefix?? && (doc.tablePrefix?length > 0)>
<#assign prefix = doc.tablePrefix + "_">
<#else>
<#assign prefix = "">
</#if>
<#-- iterate table list-->
<#list doc.tables as table>

<#-- generate package name -->
<#assign app = doc.artifactid?replace(".", "/")>
<#assign package = table.moduleid?replace(".", "/")>
<#assign fileName = table.name?cap_first>
<@pp.changeOutputFile name = "/" + package  + "/" + fileName + "DaoSqliteImpl.vb"/>
<#compress>
<#list table.fields as field>
	<#assign typePackage = getTypePackage(field.type)>
	<#if typePackage?length != 0>Imports ${typePackage}</#if>
</#list>
</#compress>
Imports Dapper
Imports System.Reflection
Imports Stone.Common
Namespace ${package?replace("/", ".")}
<#assign package = package + "." + fileName>

    ''' <summary>
    ''' ${table.comment}数据访问对象Sqlite,Dapper实现
    ''' </summary>
    ''' <remarks>${doc.version}</remarks>
    Public Class ${fileName}DaoSqliteImpl
        Implements I${fileName}Dao

		Private Const SQL_CREATE As String = "create table ${prefix}${fileName}(<#list table.fields as field>${field.name} ${jdbc2sqlite(field.type)}<#if field.contraints??><#list field.contraints as contraint> {contraint}<#if contraint_has_next> </#if></#list></#if><#if field_has_next>,</#if></#list><#if table.contraints??><#list table.contraints as contraint>${contraint}<#if contraint_has_next>,</#if></#list></#if>)"
		
        Private _Log As log4net.ILog = log4net.LogManager.GetLogger(Reflection.MethodBase.GetCurrentMethod().DeclaringType)
        Private _Connection As IDbConnection = Nothing

        Public Sub New(connection As IDbConnection)
            _Connection = connection
			init()
        End Sub

		Public Function NewID() As String
			Return Guid.NewGuid().ToString()
		End Sub
		
        Private Sub init()
            Dim count As Integer = 0

            If _Connection IsNot Nothing And _Connection.State = ConnectionState.Open Then
                '执行查询
                Try
                    count = _Connection.ExecuteScalar(Of Integer)(String.Format("select count(*) from sqlite_master where type = 'table' and Upper(name) = '{0}'", "${prefix}${fileName}".ToUpper()))
                    If (count > 0) Then
                        _Log.Info("Table ${prefix}${fileName} existed!")
                    Else
                        _Log.Info("Table ${prefix}${fileName} is not existed!")
                        Dim pks As List(Of String) = New List(Of String)()
                        pks.Add("id")

                        _Connection.Execute(SQL_CREATE)
                    End If
                Catch ex As Exception
                    _Log.Error(ex.Message)
                End Try

            End If
        End Sub

        Public Function create(obj As ${fileName}) As Outcome Implements I${fileName}Dao.create
            Dim res As Outcome = New Outcome(False)
			res.MessageId = "${app}.${package + ".Dao.connection.fail"}"
            Dim affected As Integer = 0

            If obj IsNot Nothing And _Connection IsNot Nothing And _Connection.State = ConnectionState.Open Then
                Dim parameters As New Dictionary(Of String, Object)
				If(IsNothing(obj.Id)) Then
					obj.Id = NewID()
				End If
				<#list table.fields as field>
				parameters.Add("@${field.name?uncap_first}", obj.${field.name?cap_first})
				</#list>

                Try
                    affected = _Connection.Execute("Insert into ${prefix}${fileName} (<#list table.fields as field>'${field.name?uncap_first}'<#if field_has_next>,</#if></#list>) values(<#list table.fields as field>@${field.name?uncap_first}<#if field_has_next>,</#if></#list>)", New DynamicParameters(parameters))
					res.Success = True
					res.MessageId = "${app}.${package + ".Dao.create.success"}"
					res.Data = obj.Id
                Catch ex As Exception
                    res.MessageId = "${app}.${package + ".Dao.create.fail"}"
                    res.Detail = ex.Message
                    _Log.Error(ex.Message)
                End Try
            End If

            create = res
        End Function

        Public Function createBatch(objs As List(Of ${fileName})) As Outcome Implements I${fileName}Dao.createBatch
			Dim res As Outcome = New Outcome(False)
			res.MessageId = "${app}.${package + ".Dao.connection.fail"}"
            Dim affected As Integer = 0

            If objs IsNot Nothing And _Connection IsNot Nothing And _Connection.State = ConnectionState.Open Then

                Dim params As List(Of DynamicParameters) = New List(Of DynamicParameters)
                For Each o In objs
                    Dim parameters As New Dictionary(Of String, Object)
                    If (IsNothing(o.Id)) Then
                        o.Id = NewID()
                    End If
					
					<#list table.fields as field>
					parameters.Add("@${field.name?uncap_first}", o.${field.name?cap_first})
					</#list>
					
                    params.Add(New DynamicParameters(parameters))
                Next

                Try
                    affected = _Connection.Execute("Insert into ${prefix}${fileName} (<#list table.fields as field>'${field.name?uncap_first}'<#if field_has_next>,</#if></#list>) values(<#list table.fields as field>@${field.name?uncap_first}<#if field_has_next>,</#if></#list>)", params)
                    res.Success = True
                    res.MessageId = "Stone.scalar.Scalar.Dao.createBatch.success"
                    res.Data = affected
                Catch ex As Exception
                    res.MessageId = "Stone.scalar.Scalar.Dao.createBatch.fail"
                    res.Detail = ex.Message
                    _Log.Error(ex.Message)
                End Try
            End If

            Return res
		Throw New NotImplementedException()
        End Function

        Public Function update(obj As ${fileName}) As Outcome Implements I${fileName}Dao.update
            Dim res As Outcome = New Outcome(False)
			res.MessageId = "${app}.${package + ".Dao.connection.fail"}"
            Dim affected As Integer = 0

            If obj IsNot Nothing And _Connection IsNot Nothing And _Connection.State = ConnectionState.Open Then
                Dim parameters As New Dictionary(Of String, Object)

				<#list table.fields as field>
                parameters.Add("@${field.name?uncap_first}", obj.${field.name?cap_first})
				</#list>

                Try
                    affected = _Connection.Execute("update ${prefix}${fileName} set <#list table.fields as field>${field.name?uncap_first}=@${field.name?uncap_first}<#if field_has_next>,</#if></#list> where id=@id", New DynamicParameters(parameters))
					res.Success = True
					res.MessageId = "${app}.${package + ".Dao.update.success"}"
					res.Data = affected
                Catch ex As Exception
                    res.MessageId = "${app}.${package + ".Dao.update.fail"}"
                    res.Detail = ex.Message
                    _Log.Error(ex.Message)
                End Try
            End If

            update = res
        End Function

        Public Function delete(ids As List(Of String)) As Outcome Implements I${fileName}Dao.delete
            Dim res As Outcome = New Outcome(False)
			res.MessageId = "${app}.${package + ".Dao.connection.fail"}"
            Dim affected As Integer = 0

            If _Connection IsNot Nothing And _Connection.State = ConnectionState.Open Then
				Dim ids_string As String = "("
				If ids IsNot Nothing Then
					For Each id As String In ids
						ids_string = ids_string & "'" & id & "',"
					Next

					If ids_string.EndsWith(",") Then
						ids_string = ids_string.Remove(ids_string.Length - 1)
					End If
				End If
				ids_string = ids_string & ")"

				Try
					Dim sqlDelete As String = IIf(ids_string.Length > 2, "delete from ${prefix}${fileName} where id in " & ids_string, "delete from ${prefix}${fileName}")
					affected = _Connection.Execute(sqlDelete)
					res.Success = True
					res.MessageId = "${app}.${package + ".Dao.delete.success"}"
					res.Data = affected
				Catch ex As Exception
					res.MessageId = "${app}.${package + ".Dao.delete.fail"}"
					res.Detail = ex.Message
					_Log.Error(ex.Message)
				End Try
				
			End If
			
            Return res
        End Function

        Public Function delete(id As String) As Outcome Implements I${fileName}Dao.delete
            Dim res As Outcome = New Outcome(False)
			res.MessageId = "${app}.${package + ".Dao.connection.fail"}"
            Dim affected As Integer = 0

            If _Connection IsNot Nothing And _Connection.State = ConnectionState.Open Then
				Dim ids_string As String = "("
				If id IsNot Nothing Then
					ids_string = ids_string & "'" & id & "',"
				End If
				ids_string = ids_string & ")"

				Try
					Dim sqlDelete As String = IIf(ids_string.Length > 2, "delete from ${prefix}${fileName} where id in " & ids_string, "delete from ${prefix}${fileName}")
					affected = _Connection.Execute(sqlDelete)
					res.Success = True
					res.MessageId = "${app}.${package + ".Dao.delete.success"}"
					res.Data = affected
				Catch ex As Exception
					res.MessageId = "${app}.${package + ".Dao.delete.fail"}"
					res.Detail = ex.Message
					_Log.Error(ex.Message)
				End Try
				
			End If
			
            Return res
        End Function

        Public Function delete(params As Dictionary(Of String, Object)) As Outcome Implements I${fileName}Dao.delete
            Dim res As Outcome = New Outcome(False)
			res.MessageId = "${app}.${package + ".Dao.connection.fail"}"
            Dim items As List(Of ${fileName}) = Nothing
            Dim affected As Integer = 0

            If _Connection IsNot Nothing And _Connection.State = ConnectionState.Open Then
                Dim conditions As String = "where 1=1"
                Dim parameters As Dictionary(Of String, Object) = New Dictionary(Of String, Object)

                If (Not IsNothing(params)) Then
                    If (params.ContainsKey("${fileName?lower_case}_id")) Then
                        Dim id As String = ""
                        params.TryGetValue("${fileName?lower_case}_id", id)
                        conditions = conditions & " and id=@id"
                        parameters.Add("${fileName?lower_case}_id", id)
                    End If
                End If

                '执行查询
                Try
 					affected = _Connection.Execute("delete from ${prefix}${fileName} " & conditions, New DynamicParameters(parameters))
					res.Success = True
					res.MessageId = "${app}.${package + ".Dao.delete.success"}"
					res.Data = affected
				Catch ex As Exception
					res.MessageId = "${app}.${package + ".Dao.delete.fail"}"
					res.Detail = ex.Message
					_Log.Error(ex.Message)
				End Try
				
			End If
			
            Return res
        End Function
		
        Public Function getPage(page As Integer, size As Integer, params As Dictionary(Of String, Object)) As Outcome Implements I${fileName}Dao.getPage
            Dim res As Outcome = New Outcome(False)
			res.MessageId = "${app}.${package + ".Dao.connection.fail"}"
            Dim items As List(Of ${fileName}) = Nothing

            If _Connection IsNot Nothing And _Connection.State = ConnectionState.Open Then
                Dim parameters As Dictionary(Of String, Object) = New Dictionary(Of String, Object)
                Dim conditions As String = SqliteDaoSupport.packParams(params, parameters)


                '分页参数
                conditions = conditions & " limit @size offset @size*@page"
                parameters.Add("page", page)
                parameters.Add("size", size)

                '执行查询
                Try
                    items = _Connection.Query(Of ${fileName})("select * from ${prefix}${fileName} " & conditions, New DynamicParameters(parameters))
					res.Success = True
					res.MessageId = "${app}.${package + ".Dao.getPage.success"}"
					res.Data = IIf(items IsNot Nothing, items, New List(Of ${fileName})())
                Catch ex As Exception
					res.MessageId = "${app}.${package + ".Dao.getPage.fail"}"
					res.Detail = ex.Message
					_Log.Error(ex.Message)
                End Try
            End If

			 If (IsNothing(res.Data)) Then
                res.Data = New List(Of ${fileName})()
            End If
            Return res
        End Function

        Public Function getOne(params As Dictionary(Of String, Object)) As Outcome Implements I${fileName}Dao.getOne
			Dim res As Outcome = getPage(0, 10, params)
            Dim items As List(Of ${fileName}) = res.Data

            If (items.Count > 0) Then
                res.Data = items(0)
			Else
				res.Data = Nothing
            End If

            Return res
        End Function

        Public Function size(params As Dictionary(Of String, Object)) As Outcome Implements I${fileName}Dao.size
            Dim res As Outcome = New Outcome(False)
			res.MessageId = "${app}.${package + ".Dao.connection.fail"}"
            Dim count As Integer = 0

            If _Connection IsNot Nothing And _Connection.State = ConnectionState.Open Then
                Dim parameters As New Dictionary(Of String, Object)
                Dim conditions As String = SqliteDaoSupport.packParams(params, parameters)

                '执行查询
                Try
                    count = _Connection.ExecuteScalar(Of Integer)("select count(*) from ${prefix}${fileName} " & conditions, New DynamicParameters(parameters))
					res.Success = True
					res.MessageId = "${app}.${package + ".Dao.size.success"}"
					res.Data = count
                Catch ex As Exception
					res.MessageId = "${app}.${package + ".Dao.size.fail"}"
					res.Detail = ex.Message
                    _Log.Error(ex.Message)
                End Try
            End If

            Return res
        End Function
    End Class
End Namespace

</#list>